package entity;

import database.DatabaseHelper;

import javax.xml.crypto.Data;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class Section {

    private String id;
    private int maxSize;
    private String semester;
    private String couId;
    private String teaId;

    public String getId() {
        return id;
    }

    public int getMaxSize() {
        return maxSize;
    }

    public String getSemester() {
        return semester;
    }

    public Section(String id, int maxSize, String semester, String couId, String teaId) {
        this.id = id;
        this.maxSize = maxSize;
        this.semester = semester;
        this.couId = couId;
        this.teaId = teaId;
    }

    /* C */
    public static boolean create(DatabaseHelper db, String id, int maxSize, String semester, Course cou, Teacher tea) throws SQLException {
        String update = "insert into section(id, size, semester, cou_id, tea_id) value (?, ?, ?, ?, ?)";
        return db.update(update, id, maxSize, semester, cou.getId(), tea.getId()) > 0;
    }

    /* R */
    public static ArrayList<Section> findWhere(DatabaseHelper db, String condition, Object... params) throws SQLException {
        String query = "select id,size,semester,cou_id,tea_id from section where " + condition;
        ResultSet rs = db.query(query, params);
        ArrayList<Section> sections = new ArrayList<>();
        while (rs.next()) {
            sections.add(new Section(rs.getString(1), rs.getInt(2), rs.getString(3), rs.getString(4), rs.getString(5)));
        }
        rs.close();
        return sections;
    }

    public static Section find(DatabaseHelper db, String id) throws SQLException {
        ArrayList<Section> sections = findWhere(db, "id = ?", id);
        return sections.isEmpty() ? null : sections.get(0);
    }

    public Course getCourse(DatabaseHelper db) throws SQLException {
        return Course.find(db, couId);
    }

    public Teacher getTeacher(DatabaseHelper db) throws SQLException {
        return Teacher.find(db, teaId);
    }

    public ArrayList<Student> getStudents(DatabaseHelper db) throws SQLException {
        return Student.findWhere(db, "id in (select stu_id from take where sec_id = ?)", id);
    }

    public boolean hasStudent(DatabaseHelper db, Student stu) throws SQLException {
        ArrayList<Student> students = Student.findWhere(db, "id = ? and id in (select stu_id from take where sec_id = ?)", stu.getId(), id);
        return !students.isEmpty();
    }

    public int countStudents(DatabaseHelper db) throws SQLException {
        String query = "select count(*) from take where sec_id = ?";
        ResultSet rs = db.query(query, id);
        rs.next();
        return rs.getInt(1);
    }

    public boolean refresh(DatabaseHelper db) throws SQLException {
        Section section = find(db, id);
        if (section == null)
            return false;
        maxSize = section.maxSize;
        semester = section.semester;
        couId = section.couId;
        teaId = section.teaId;
        return true;
    }

    /* U */
    public boolean setAttribute(DatabaseHelper db, String attribute, Object value) throws SQLException {
        String update = "update section set "+attribute+" = ? where id = ?";
        return db.update(update, value, id) > 0;
    }

    public boolean setMaxSize(DatabaseHelper db, int maxSize) throws SQLException {
        return setAttribute(db, "size", maxSize);
    }

    public boolean setSemester(DatabaseHelper db, String semester) throws SQLException {
        return setAttribute(db, "semester", semester);
    }

    public boolean addStudent(DatabaseHelper db, Student stu) throws SQLException {
        String update = "insert into take(sec_id, stu_id) value (?,?)";
        return db.update(update, id, stu.getId()) > 0;
    }

    /* D */
    public boolean delete(DatabaseHelper db) throws SQLException {
        return db.update("delete from section where id = ?", id) > 0;
    }

}
